﻿
CREATE PROCEDURE [dbo].[CreazaArhivaFares]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strTabelaAn varchar(40)
	DECLARE @Comanda varchar(8000)
	DECLARE @existTable bit
	
	SET @strTabelaAn = cast(@An AS varchar(4)) 
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'Documente' + @strTabelaAn)
		SET @existTable = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [dbo].[Documente'+@strTabelaAn+'](
		[IDDocument] [bigint] NOT NULL,
		[NrDoc] [varchar](20) NOT NULL,
		[DataDoc] [datetime] NOT NULL,
		[IDPartener] [bigint] NOT NULL,
		[CodUnitate] [smallint] NOT NULL DEFAULT 0,
		[CotaTVA] [decimal](5, 2) NOT NULL DEFAULT 0,
		[IDTipDocument] [smallint] NOT NULL DEFAULT 0,
		[TotalValoare] [decimal](25, 5) NOT NULL DEFAULT 0,
		[TotalValoareTVA] [decimal](25, 5) NOT NULL DEFAULT 0,
		[Total] [decimal](25, 5) NOT NULL DEFAULT 0,
		[IDRegimFiscal] [smallint] NOT NULL DEFAULT 0,
		[UserId] [uniqueidentifier] NULL,
		[IDDepartament] [int] NULL DEFAULT 0,
		[NrContract] [varchar](50) NULL,
		[DataContract] [datetime] NULL,
		[Observatii] [varchar](250) NULL,
		[DataScadenta] [datetime] NULL,
		[ValoareTotalaCuAdaos] [decimal](25, 5) NOT NULL DEFAULT 0,
		[NrDocReceptie] [varchar](20) NULL,
		[DataDocReceptie] [datetime] NULL,
		[IDGestionar] [int] NULL,
		[IDPredator] [int] NULL,
		[IDJurnal] [int] NULL,
		[CursValutar] [decimal](18, 4) NOT NULL,
		[CodValuta] [smallint] NOT NULL,
		[AreDocGestStoc] [bit] NOT NULL,
		[DataStartProductie] [datetime] NOT NULL,
	 CONSTRAINT [PK_Documente'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocument] ASC
	),
	 CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data'+@strTabelaAn+'] UNIQUE NONCLUSTERED 
	(
		[IDPartener] ASC,
		[CodUnitate] ASC,
		[NrDoc] ASC,
		[DataDoc] ASC
	),
	 CONSTRAINT [FK_Documente_Departamente'+@strTabelaAn+'] FOREIGN KEY ([IDDepartament]) REFERENCES [dbo].[Departamente] ([IDDepartament]),
	 CONSTRAINT [FK_Documente_Parteneri'+@strTabelaAn+'] FOREIGN KEY([IDPartener]) REFERENCES [dbo].[Parteneri] ([IDPartener]),
	 CONSTRAINT [FK_Documente_RegimFiscal'+@strTabelaAn+'] FOREIGN KEY([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
	 CONSTRAINT [FK_Documente_TipDocument'+@strTabelaAn+'] FOREIGN KEY([IDTipDocument]) REFERENCES [dbo].[TipDocument] ([IDTipDocument]),
	 CONSTRAINT [FK_Documente_Unitati'+@strTabelaAn+'] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	 CONSTRAINT [FK_Documente_Jurnale'+@strTabelaAn+'] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	 CONSTRAINT [FK_Documente_Valuta'+@strTabelaAn+'] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta]),
	 CONSTRAINT [FK_Documente_Gestionari'+@strTabelaAn+'] FOREIGN KEY([IDGestionar]) REFERENCES [dbo].[Gestionari] ([IDGestionar])
	) ON [PRIMARY]'
		
	IF @existTable = 0
		EXEC(@Comanda)
		

	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumenteDetaliu'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumenteDetaliu'+@strTabelaAn+'](
		[IDDocumentDetaliu] [bigint] NOT NULL,
		[IDDocument] [bigint] NOT NULL,
		[IDArticol] [int] NOT NULL,
		[Cantitate] [decimal](25, 5) NOT NULL DEFAULT 0,
		[ValoareUnitara] [decimal](25, 5) NOT NULL  DEFAULT 0,
		[ValoareTVA] [decimal](25, 5) NOT NULL  DEFAULT 0,
		[ValoareTotala] [decimal](25, 5) NOT NULL,
		[CodUnitate] [smallint] NOT NULL  DEFAULT 0,
		[IDUnitateMasura] [smallint] NOT NULL  DEFAULT 0,
		[IDRegimFiscal] [smallint] NOT NULL  DEFAULT 0,
		[CotaTVA] [decimal](5, 2) NOT NULL DEFAULT 0,
		[TermenLivrare] [datetime] NULL,
		[IDVariantaArticol] [int] NULL,
		[Observatii] [varchar](250) NULL,
		[ValoareTotalaCuAdaos] [decimal](25, 5) NOT NULL DEFAULT 0,
		[ArtAl] [varchar](40) NULL,
		[ValoareUnitaraValuta] [decimal](25, 5) NOT NULL,
	 CONSTRAINT [PK_DocumenteDetaliu'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentDetaliu] ASC
	),
	CONSTRAINT [FK_DocumenteDetaliu_Articole'+@strTabelaAn+'] FOREIGN KEY([IDArticol]) REFERENCES [dbo].[Articole] ([IDArticol]),
	CONSTRAINT [FK_DocumenteDetaliu_ArticoleVariante'+@strTabelaAn+'] FOREIGN KEY([IDVariantaArticol])REFERENCES [dbo].[ArticoleVariante] ([IDVariantaArticol]),
	CONSTRAINT [FK_DocumenteDetaliu_Documente'+@strTabelaAn+'] FOREIGN KEY([IDDocument]) REFERENCES [dbo].[Documente'+@strTabelaAn+'] ([IDDocument]),
	CONSTRAINT [FK_DocumenteDetaliu_RegimFiscal'+@strTabelaAn+'] FOREIGN KEY([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
	CONSTRAINT [FK_DocumenteDetaliu_Unitati'+@strTabelaAn+'] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_DocumenteDetaliu_UnitatiMasura'+@strTabelaAn+'] FOREIGN KEY([IDUnitateMasura]) REFERENCES [dbo].[UnitatiMasura] ([IDUnitateMasura]),
	) ON [PRIMARY]'
	
	IF @existTable = 0
		EXEC(@Comanda)
		
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumentePlata'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumentePlata'+@strTabelaAn+'](
		[IDDocumentPlata] [int] NOT NULL,
		[Numar] [varchar](20) NOT NULL,
		[Data] [datetime] NOT NULL,
		[IDTipPlata] [smallint] NOT NULL,
		[ValoareTotala] [decimal](18, 2) NOT NULL DEFAULT 0,
		[IDPartener] [bigint] NOT NULL,
		[Reprezentand] [varchar](250) NULL,
		[TipOperatiune] [varchar](10) NOT NULL DEFAULT (''INC''),
		[CodUnitate] [smallint] NOT NULL DEFAULT 0,
		[DataExtras] [datetime] NULL,
		[IDOrdonantare] [int] NULL,
	 CONSTRAINT [PK_DocumentePlata'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentPlata] ASC
	),
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_Parteneri] FOREIGN KEY([IDPartener]) REFERENCES [dbo].[Parteneri] ([IDPartener]),
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_TipDocumentPlata] FOREIGN KEY([IDTipPlata]) REFERENCES [dbo].[TipDocumentPlata] ([IDTipPlata]),
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	) ON [PRIMARY]'

	IF @existTable = 0
		EXEC(@Comanda)
		
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumentePlataDetaliu'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumentePlataDetaliu'+@strTabelaAn+'](
		[IDDocPlataDetaliu] [int] NOT NULL,
		[IDDocument] [bigint] NULL,
		[IDDocumentPlata] [int] NOT NULL,
		[Valoare] [decimal](18, 2) NOT NULL DEFAULT 0,
	 CONSTRAINT [PK_DocumentePlataDetaliu'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocPlataDetaliu] ASC
	),
		CONSTRAINT [FK_DocumentePlataDetaliu'+@strTabelaAn+'_DocumentePlata'+@strTabelaAn+'] FOREIGN KEY([IDDocumentPlata]) REFERENCES [dbo].[DocumentePlata'+@strTabelaAn+'] ([IDDocumentPlata]),
	) ON [PRIMARY]'

	IF @existTable = 0
			EXEC(@Comanda)
			
	SET @existTable = 0
	IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[fares_DocumentePlatite'+@strTabelaAn+']'))
		SET @existTable = 1

	SET @Comanda = 'CREATE VIEW [dbo].[fares_DocumentePlatite'+@strTabelaAn+']
			AS
	SELECT     a.IDDocPlataDetaliu, a.IDDocumentPlata, a.Valoare, 
						  b.NrDoc + ''/'' + CONVERT(varchar(20), b.DataDoc, 101) AS NrDataDoc, a.IDDocument
	FROM         dbo.DocumentePlataDetaliu'+@strTabelaAn+' a INNER JOIN
						  dbo.Documente'+@strTabelaAn+'  b ON a.IDDocument = b.IDDocument'		
    IF @existTable = 0
			EXEC(@Comanda)
		
END